package com.hnfnu.mapper;

import com.hnfnu.protocol.dto.*;
import com.hnfnu.entity.Donations;
import com.hnfnu.protocol.vo.DonationsGoalAndRaisedVO;
import com.hnfnu.protocol.vo.NumberOfDonorsVO;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface DonationsMapper {
    @Insert("insert into donations (trade_no, amount, user_id, username, message, project_id, create_time, status) values (#{tradeNo}, #{amount}, #{userId}, #{username}, #{message}, #{projectId}, #{createTime}, #{status})")
    boolean insert(Donations donations);

    @Select("SELECT d.id, d.amount, d.username, d.message, p.name AS projectName, d.create_time AS createTime\n" +
            "FROM project AS p\n" +
            "JOIN donations AS d ON p.id = d.project_id;")
    List<DonationsPageDTO> page();

    @Select("SELECT levels.amount_level,IFNULL(COUNT(d.amount), 0) AS counts FROM (SELECT '1/<50' AS amount_level\n" +
            "      UNION ALL SELECT '2/50-100'\n" +
            "      UNION ALL SELECT '3/100-150'\n" +
            "      UNION ALL SELECT '4/150-200'\n" +
            "      UNION ALL SELECT '5/200-250'\n" +
            "      UNION ALL SELECT '6/250-300'\n" +
            "      UNION ALL SELECT '7/>300') AS levels\n" +
            "         LEFT JOIN donations d ON\n" +
            "        CASE\n" +
            "            WHEN d.amount < 50 THEN '1/<50'\n" +
            "            WHEN d.amount >= 50 AND d.amount < 100 THEN '2/50-100'\n" +
            "            WHEN d.amount >= 100 AND d.amount < 150 THEN '3/100-150'\n" +
            "            WHEN d.amount >= 150 AND d.amount < 200 THEN '4/150-200'\n" +
            "            WHEN d.amount >= 200 AND d.amount < 250 THEN '5/200-250'\n" +
            "            WHEN d.amount >= 250 AND d.amount < 300 THEN '6/250-300'\n" +
            "            WHEN d.amount >= 300 THEN '7/>300'\n" +
            "            END = levels.amount_level\n" +
            "GROUP BY levels.amount_level\n" +
            "ORDER BY levels.amount_level;")
    List<DonationsIntervalDTO> getIntervalNUmber();

    @Select("SELECT\n" +
            "    years.year,\n" +
            "    months.month,\n" +
            "    COALESCE(SUM(donations.amount), 0) AS amounts\n" +
            "FROM\n" +
            "    (SELECT YEAR(CURDATE()) AS year\n" +
            "    UNION\n" +
            "    SELECT YEAR(CURDATE()) - 1\n" +
            "    UNION\n" +
            "    SELECT YEAR(CURDATE()) - 2\n" +
            "    UNION\n" +
            "    SELECT YEAR(CURDATE()) - 3\n" +
            "    UNION\n" +
            "    SELECT YEAR(CURDATE()) - 4) AS years\n" +
            "CROSS JOIN\n" +
            "    (SELECT 1 AS month\n" +
            "    UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6\n" +
            "    UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) AS months\n" +
            "LEFT JOIN\n" +
            "    donations ON YEAR(donations.create_time) = years.year AND MONTH(donations.create_time) = months.month\n" +
            "GROUP BY\n" +
            "    years.year, months.month\n" +
            "ORDER BY\n" +
            "    years.year, months.month;")
    List<DonationsMonthTrendDTO> getMonthTrend();

    @Select("SELECT project_id as projectId,\n" +
            "       SUM(amount) AS amounts\n" +
            "FROM donations\n" +
            "GROUP BY project_id;")
    List<DonationsProjectIdAmountsDTO> getProjectIdAmounts();

    @Select("select sum(amount) as totalAmount from donations where status = '1'")
    Double getTotalAmount();

    @Select("SELECT provinces.province_name                        as province,\n" +
            "       COALESCE(COUNT(DISTINCT donations.user_id), 0) AS counts,\n" +
            "       COALESCE(SUM(donations.amount), 0)             AS amounts\n" +
            "FROM provinces\n" +
            "         LEFT JOIN\n" +
            "     user ON SUBSTRING_INDEX(address, '-', 1) = provinces.name\n" +
            "         LEFT JOIN\n" +
            "     donations ON donations.user_id = user.id\n" +
            "GROUP BY provinces.province_name;")
    List<DonationsProvinceDTO> getProvinceNumber();

    @Select("select grade, count from satisfaction")
    List<DonationsSatisfactionDTO> getSatisfaction();

    @Update("update satisfaction set count = count + 1 where grade = #{grade}")
    boolean addSatisfaction(Integer grade);

    @Select("SELECT pu.name AS name, COALESCE(SUM(do.amount), 0) AS totalAmount\n" +
            "FROM purpose pu\n" +
            "LEFT JOIN purpose_detail pd ON pu.id = pd.purpose_id\n" +
            "LEFT JOIN project pr ON pd.id = pr.purpose_detail_id\n" +
            "LEFT JOIN donations do ON pr.id = do.project_id\n" +
            "GROUP BY pu.name;")
    List<DonationsPurposeDTO> getPurposeTotalAmount();

    @Select("SELECT pd.name AS name, pd.purpose_id AS purposeId, COALESCE(SUM(do.amount), 0) AS totalAmount\n" +
            "FROM purpose_detail pd\n" +
            "LEFT JOIN project pr ON pd.id = pr.purpose_detail_id\n" +
            "LEFT JOIN donations do ON pr.id = do.project_id\n" +
            "GROUP BY pd.name, pd.purpose_id;")
    List<DonationsPurposeDetailAmountDTO> getPurposeDetailAmount();

    @Select("SELECT\n" +
            "    COUNT(DISTINCT user_id) AS todayNumber,\n" +
            "    (SELECT COUNT(DISTINCT user_id) FROM donations WHERE project_id = #{projectId}) AS totalNumber\n" +
            "FROM\n" +
            "    donations\n" +
            "WHERE\n" +
            "    project_id = #{projectId}\n" +
            "    AND DATE(create_time) = CURDATE();")
    NumberOfDonorsVO getNumberOfDonors(Integer projectId);
    @Select("select count(*) from user where sex = 1")
    int getMaleNumber();
    @Select("select count(*) from user where sex = 0")
    int getFeMaleNumber();

    @Select("select * from donations where trade_no = #{tradeNo}")
    Donations getDonationByTrade(String tradeNo);

    @Update("update donations set status = '1' where trade_no = #{tradeNO}")
    void updateStatus(String tradeNo);

    @Select("SELECT SUM(d.amount) AS raised\n" +
            "            FROM donations d\n" +
            "                     JOIN project p ON d.project_id = p.id\n" +
            "            WHERE d.project_id = #{projectId} and status = '1';")
    Double getRaised(Integer projectId);

    @Select("select amount, user_id as userId\n" +
            "from donations\n" +
            "where project_id = #{projectId}\n" +
            "  and status = '1' and spend = 0\n" +
            "order by create_time;")
    List<DonationsOrderByTimeDTO> selectAmountAndUserIdOrderByTime(Integer projectId);

    @Update("update donations set spend = 1 where user_id = #{userId} and project_id = #{projectId};")
    void updateSpendByUserIdAndProjectId(@Param("userId") Integer userId, @Param("projectId") Integer projectId);
    @Select("SELECT COUNT(*) \n" +
            "FROM user\n" +
            "WHERE birthday >= DATE_SUB(CURDATE(), INTERVAL #{r} YEAR) \n" +
            "  AND birthday < DATE_SUB(CURDATE(), INTERVAL #{l} YEAR);")
    int getAgeInvalNumber(int l, int r);

    @Select("select count(*) from user")
    int getTotalNumberOfUsers();

    @Select("select count(*) from (select name,sum(amount) as 'current',goal from donations,project where project_id = project.id group by project_id) as derived where current >= goal")
    int getEndStep();

    @Select("select count(*) from (select name,sum(amount) as 'current',goal from donations,project where project_id = project.id group by project_id) as derived where current < goal")
    int getMiddleStep();

    @Select("select count(*) from project where id  not in (select project_id from donations);")
    int getStartStep();

    @Select("SELECT COALESCE(count(*), 0) as totalMoney\n" +
            "            FROM donations\n" +
            "            WHERE YEARWEEK(create_time, 1) = YEARWEEK(CURDATE(), 1) AND status = 1")
    int getWeekDonationNum();
}
